package cz.kfad.ofrss.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import cz.kfad.ofrss.model.Category;
import cz.kfad.ofrss.model.Channel;
import cz.kfad.ofrss.model.RssFeed;
import cz.kfad.ofrss.model.RssFeedShort;
import cz.kfad.ofrss.rss.CategoriesScanner;

import java.util.Date;
import java.util.List;

public class DatabaseHelper extends AbstractDatabaseHelper {

    private final Context context;

    public static int MAX_CONTENT_ATTEMPTS = 10;

    public DatabaseHelper(Context context) {
        super(context);
        this.context = context;
    }

    public void saveRssFeeds(final List<RssFeed> rssFeeds) {
        final CategoriesScanner categoriesScanner = new CategoriesScanner(context);

        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                for (RssFeed rssFeed : rssFeeds) {
                    final ContentValues contentValues = new ContentValues();
                    contentValues.put(DbConstants.COLUMN_RSS_FEED_TITLE, rssFeed.getTitle());
                    contentValues.put(DbConstants.COLUMN_RSS_FEED_URL, rssFeed.getUrl());
                    contentValues.put(DbConstants.COLUMN_RSS_FEED_TEXT, rssFeed.getText());
                    contentValues.put(DbConstants.COLUMN_RSS_FEED_CHANNEL_ID, rssFeed.getChannelId());
                    contentValues.put(DbConstants.COLUMN_RSS_FEED_PUBDATE, Long.toString(rssFeed.getDate().getTime()));
                    contentValues.put(DbConstants.COLUMN_RSS_FEED_READ, 0);
                    contentValues.put(DbConstants.COLUMN_RSS_FEED_CONTENT, rssFeed.getContent());
                    contentValues.put(DbConstants.COLUMN_RSS_FEED_CONTENT_ATTEMPTS, rssFeed.getContentAttempts());
                    final int insertedId = (int) database.insert(DbConstants.TABLE_RSS_FEED, null, contentValues);

                    final List<Category> categories = categoriesScanner.findCategories(rssFeed);
                    for (final Category category : categories) {
                        final int categoryId = category.getId();
                        saveCategoryFeed(database, insertedId, categoryId, true);
                    }
                }
            }
        });
    }

    public void saveCategoryFeed(final int feedId, final int categoryId, final boolean increaseUnreadCount) {
        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                saveCategoryFeed(database, feedId, categoryId, increaseUnreadCount);
            }
        });
    }

    private void saveCategoryFeed(SQLiteDatabase database, int feedId, int categoryId, boolean increaseUnreadCount) {
        final ContentValues values = new ContentValues();
        values.put(DbConstants.COLUMN_CATEGORY_FEED_CATEGORY_ID, categoryId);
        values.put(DbConstants.COLUMN_CATEGORY_FEED_FEED_ID, feedId);
        database.insert(DbConstants.TABLE_CATEGORY_FEED, null, values);

        String sqlToIncreaseCategoriesCount =
                "UPDATE " + DbConstants.TABLE_CATEGORY + " SET " +
                        DbConstants.COLUMN_CATEGORY_TOTAL_FEEDS + "=1+" + DbConstants.COLUMN_CATEGORY_TOTAL_FEEDS;
        if (increaseUnreadCount) {
            sqlToIncreaseCategoriesCount += " , " + DbConstants.COLUMN_CATEGORY_UNREAD_FEEDS + "=1+" + DbConstants.COLUMN_CATEGORY_UNREAD_FEEDS;
        }
        sqlToIncreaseCategoriesCount += " WHERE " + DbConstants.COLUMN_CATEGORY_ID + "=?";

        final SQLiteStatement sqLiteStatement = database.compileStatement(sqlToIncreaseCategoriesCount);
        sqLiteStatement.bindString(1, Integer.toString(categoryId));
        sqLiteStatement.execute();
    }

    public List<Category> findAllCategories() {
        return doWithReadableDatabase(new ActionWithResultWithDatabase() {
            @Override
            public List<Category> doAction(SQLiteDatabase database) {
                final Cursor cursor = database.query(DbConstants.TABLE_CATEGORY, null, null, null, null, null, DbConstants.COLUMN_CATEGORY_NAME, null);
                final List<Category> categories = CursorToListConverterUtil.toCategoryList(cursor);
                cursor.close();
                return categories;
            }
        });
    }

    public List<Channel> findAllChannels() {
        return doWithReadableDatabase(new ActionWithResultWithDatabase() {
            @Override
            public List<Channel> doAction(SQLiteDatabase database) {
                final Cursor cursor = database.query(DbConstants.TABLE_CHANNEL, null, null, null, null, null, DbConstants.COLUMN_CHANNEL_NAME, null);
                final List<Channel> channels = CursorToListConverterUtil.toChannelList(cursor);
                cursor.close();
                return channels;
            }
        });
    }

    public RssFeed findFeed(final int feedId) {
        return doWithReadableDatabase(new ActionWithResultWithDatabase() {
            @Override
            public RssFeed doAction(SQLiteDatabase database) {
                final Cursor cursor = database.query(DbConstants.TABLE_RSS_FEED, null, DbConstants.COLUMN_RSS_FEED_ID + "=?",
                        new String[]{Integer.toString(feedId)}, null, null, null, null);
                final List<RssFeed> rssFeeds = CursorToListConverterUtil.toRssList(cursor);
                cursor.close();
                if (rssFeeds.size() != 1) {
                    throw new RuntimeException("expected one record for feedId " + feedId);
                }
                return rssFeeds.get(0);
            }
        });
    }

    public List<RssFeedShort> findFeedByChannel(final int channelId) {
        return doWithReadableDatabase(new ActionWithResultWithDatabase() {
            @Override
            public List<RssFeedShort> doAction(SQLiteDatabase database) {
                final Cursor cursor = database.query(DbConstants.TABLE_RSS_FEED,
                        CursorToListConverterUtil.getColumnsForRssFeedShort()
                        , DbConstants.COLUMN_RSS_FEED_CHANNEL_ID + "=?", new String[]{Integer.toString(channelId)},
                        null, null, null, null);
                final List<RssFeedShort> rssFeeds = CursorToListConverterUtil.toRssShortList(cursor);
                cursor.close();
                return rssFeeds;
            }
        });
    }

    public Channel findChannelById(final int channelId) {
        return doWithReadableDatabase(new ActionWithResultWithDatabase() {
            @Override
            public Channel doAction(SQLiteDatabase database) {
                final Cursor cursor = database.query(DbConstants.TABLE_CHANNEL, null, DbConstants.COLUMN_CHANNEL_ID + "=?",
                        new String[]{Integer.toString(channelId)}, null, null, null, null);
                final List<Channel> channels = CursorToListConverterUtil.toChannelList(cursor);
                cursor.close();
                if (channels.size() != 1) {
                    throw new RuntimeException("expected one record,but it is " + channels.size());
                }
                return channels.get(0);
            }
        });
    }

    public Category findCategoryById(final int categoryId) {
        return doWithReadableDatabase(new ActionWithResultWithDatabase() {
            @Override
            public Category doAction(SQLiteDatabase database) {
                final Cursor cursor = database.query(DbConstants.TABLE_CATEGORY, null, DbConstants.COLUMN_CATEGORY_ID + "=?",
                        new String[]{Integer.toString(categoryId)}, null, null, null, null);
                final List<Category> categories = CursorToListConverterUtil.toCategoryList(cursor);
                cursor.close();
                if (categories.size() != 1) {
                    throw new RuntimeException("expected one record");
                }
                return categories.get(0);
            }
        });
    }

    public void saveChannel(final Channel channel) {
        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                final ContentValues contentValues = new ContentValues();
                contentValues.put(DbConstants.COLUMN_CHANNEL_NAME, channel.getName());
                contentValues.put(DbConstants.COLUMN_CHANNEL_URL, channel.getUrl());
                contentValues.put(DbConstants.COLUMN_CHANNEL_NEWEST_FETCHED, new Date().getTime() - (60 * 60 * 1000 * 24 * 3));
                final long insert = database.insert(DbConstants.TABLE_CHANNEL, null, contentValues);
                channel.setId((int) insert);
            }
        });
    }

    public void deleteChannelById(final int channelId) {
        final List<RssFeedShort> feedByChannel = findFeedByChannel(channelId);
        for (final RssFeedShort rssFeedShort : feedByChannel) {
            final int rssFeedShortId = rssFeedShort.getId();
            deleteRssFeedById(rssFeedShortId);
        }

        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                database.delete(DbConstants.TABLE_CHANNEL, DbConstants.COLUMN_CHANNEL_ID + "=?",
                        new String[]{Integer.toString(channelId)});
            }
        });
    }

    public void deleteCategoryById(final int categoryId) {
        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                database.delete(DbConstants.TABLE_CATEGORY, DbConstants.COLUMN_CATEGORY_ID + "=?",
                        new String[]{Integer.toString(categoryId)});

                database.delete(DbConstants.TABLE_CATEGORY_FEED, DbConstants.COLUMN_CATEGORY_FEED_CATEGORY_ID + "=?",
                        new String[]{Integer.toString(categoryId)});
            }
        });
    }

    public void deleteRssFeedById(final int itemId) {
        decreaseTotalCountForFeedInCategories(itemId);
        decreaseTotalCountForFeedInChannel(itemId);
        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                database.delete(DbConstants.TABLE_RSS_FEED, DbConstants.COLUMN_RSS_FEED_ID + "=?",
                        new String[]{Integer.toString(itemId)});
            }
        });

    }


    public void saveRssFeedIsRead(final int feedId) {
        final RssFeed feed = findFeed(feedId);
        final boolean read = feed.isRead();

        if (!read) {
            doWithWritableDatabase(new ActionWithDatabase() {
                @Override
                public void doAction(SQLiteDatabase database) {
                    ContentValues updatedValues = new ContentValues();
                    updatedValues.put(DbConstants.COLUMN_RSS_FEED_READ, 1);

                    database.update(
                            DbConstants.TABLE_RSS_FEED,
                            updatedValues,
                            DbConstants.COLUMN_RSS_FEED_ID + "=?",
                            new String[]{Integer.toString(feedId)});
                }
            });
            decreaseUnreadCountForFeedInChannel(feedId);
            decreaseUnreadCountForFeedInCategories(feedId);
        }
    }

    private void decreaseUnreadCountForFeedInCategories(final int feedId) {
        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {

                final String query =
                        "SELECT " +
                                DbConstants.COLUMN_CATEGORY_FEED_CATEGORY_ID +//PROJECTION
                                " FROM " + DbConstants.TABLE_CATEGORY_FEED +
                                " WHERE " + DbConstants.COLUMN_CATEGORY_FEED_FEED_ID + "=?";

                final String sql = "UPDATE " + DbConstants.TABLE_CATEGORY + " SET " +
                        DbConstants.COLUMN_CATEGORY_UNREAD_FEEDS + "=" + DbConstants.COLUMN_CATEGORY_UNREAD_FEEDS + "-1 " +
                        " WHERE " + DbConstants.COLUMN_CATEGORY_ID + " IN (" + query + ")";

                final SQLiteStatement statement = database.compileStatement(sql);
                statement.bindString(1, Integer.toString(feedId));
                statement.execute();
            }
        });
    }

    private void decreaseUnreadCountForFeedInChannel(final int feedId) {
        final RssFeed feed = findFeed(feedId);
        final int channelId = feed.getChannelId();

        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                database.execSQL("UPDATE " + DbConstants.TABLE_CHANNEL + " SET " +
                        DbConstants.COLUMN_CHANNEL_UNREAD_FEEDS + "=" + DbConstants.COLUMN_CHANNEL_UNREAD_FEEDS + "-1 " +
                        " WHERE " + DbConstants.COLUMN_CHANNEL_ID + "=" + channelId);
            }
        });
    }

    private void decreaseTotalCountForFeedInChannel(int feedId) {
        final RssFeed feed = findFeed(feedId);
        final int channelId = feed.getChannelId();

        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                database.execSQL("UPDATE " + DbConstants.TABLE_CHANNEL + " SET " +
                        DbConstants.COLUMN_CHANNEL_TOTAL_FEEDS + "=" + DbConstants.COLUMN_CHANNEL_TOTAL_FEEDS + "-1 " +
                        " WHERE " + DbConstants.COLUMN_CHANNEL_ID + "=" + channelId);
            }
        });
    }

    private void decreaseTotalCountForFeedInCategories(final int feedId) {
        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                final String query =
                        "SELECT " +
                                DbConstants.COLUMN_CATEGORY_FEED_CATEGORY_ID +//PROJECTION
                                " FROM " + DbConstants.TABLE_CATEGORY_FEED +
                                " WHERE " + DbConstants.COLUMN_CATEGORY_FEED_FEED_ID + "=?";

                final String sql = "UPDATE " + DbConstants.TABLE_CATEGORY + " SET " +
                        DbConstants.COLUMN_CATEGORY_TOTAL_FEEDS + "=" + DbConstants.COLUMN_CATEGORY_TOTAL_FEEDS + "-1 " +
                        " WHERE " + DbConstants.COLUMN_CATEGORY_ID + " IN (" + query + ")";

                final SQLiteStatement statement = database.compileStatement(sql);
                statement.bindString(1, Integer.toString(feedId));
                statement.execute();
            }
        });
    }

    public List<RssFeedShort> findFeedByCategory(final int categoryId) {
        return doWithReadableDatabase(new ActionWithResultWithDatabase() {
            @Override
            public List<RssFeedShort> doAction(SQLiteDatabase database) {
                final String[] columnsForRssFeedShort = CursorToListConverterUtil.getColumnsForRssFeedShort();
                String projection = "";
                for (final String s : columnsForRssFeedShort) {
                    projection += s + " , ";
                }
                projection = projection.substring(0, projection.length() - 2);

                final String query =
                        "SELECT " +
                                projection +//PROJECTION
//                                "*" +//PROJECTION
                                " FROM " + DbConstants.TABLE_CATEGORY_FEED + " , " + DbConstants.TABLE_RSS_FEED +
                                " WHERE " + DbConstants.COLUMN_CATEGORY_FEED_CATEGORY_ID + "=?" +
                                " AND " + DbConstants.COLUMN_CATEGORY_FEED_FEED_ID + "=" + DbConstants.COLUMN_RSS_FEED_ID;//join condition

                final Cursor cursor = database.rawQuery(query, new String[]{Integer.toString(categoryId)});
                final List<RssFeedShort> rssFeeds = CursorToListConverterUtil.toRssShortList(cursor);
                cursor.close();
                return rssFeeds;
            }
        });
    }

    public void increaseCountsForChannel(final int channelId, final int size) {

        final Channel channelById = findChannelById(channelId);
        final int newTotalFeeds = channelById.getTotalFeeds() + size;
        final int newUnreadFeeds = channelById.getUnreadFeeds() + size;

        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                final ContentValues updatedValues = new ContentValues();
                updatedValues.put(DbConstants.COLUMN_CHANNEL_TOTAL_FEEDS, newTotalFeeds);
                updatedValues.put(DbConstants.COLUMN_CHANNEL_UNREAD_FEEDS, newUnreadFeeds);

                database.update(
                        DbConstants.TABLE_CHANNEL,
                        updatedValues,
                        DbConstants.COLUMN_CHANNEL_ID + "=?",
                        new String[]{Integer.toString(channelId)});

            }
        });
    }

    public void saveCategory(final Category category) {
        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                final ContentValues contentValues = new ContentValues();
                contentValues.put(DbConstants.COLUMN_CATEGORY_NAME, category.getName());
                contentValues.put(DbConstants.COLUMN_CATEGORY_KEY, category.getKey());
                contentValues.put(DbConstants.COLUMN_CATEGORY_UNREAD_FEEDS, 0);
                contentValues.put(DbConstants.COLUMN_CATEGORY_TOTAL_FEEDS, 0);
                final long insert = database.insert(DbConstants.TABLE_CATEGORY, null, contentValues);
                category.setId((int) insert);
            }
        });
    }

    public List<RssFeed> findAllFeeds() {
        return doWithReadableDatabase(new ActionWithResultWithDatabase() {
            @Override
            public List<RssFeed> doAction(SQLiteDatabase database) {
                final Cursor cursor = database.query(DbConstants.TABLE_RSS_FEED, null, null, null, null, null, null);
                List<RssFeed> rssList = CursorToListConverterUtil.toRssList(cursor);
                cursor.close();
                return rssList;
            }
        });
    }

    public List<RssFeedShort> findAllFeeds(final String orderBy) {
        return doWithReadableDatabase(new ActionWithResultWithDatabase() {

            @Override
            public List<RssFeedShort> doAction(SQLiteDatabase database) {
                final Cursor cursor = database.query(DbConstants.TABLE_RSS_FEED, CursorToListConverterUtil.getColumnsForRssFeedShort(),
                        null, null, null, null, orderBy, null);
                List<RssFeedShort> rssList = CursorToListConverterUtil.toRssShortList(cursor);
                cursor.close();
                return rssList;
            }
        });
    }

    public void editChannel(final Channel channel) {
        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                final ContentValues contentValues = new ContentValues();
                contentValues.put(DbConstants.COLUMN_CHANNEL_NAME, channel.getName());
                contentValues.put(DbConstants.COLUMN_CHANNEL_URL, channel.getUrl());
                database.update(DbConstants.TABLE_CHANNEL, contentValues, DbConstants.COLUMN_CHANNEL_ID + "=?",
                        new String[]{Integer.toString(channel.getId())});
            }
        });
    }

    public void editCategory(final Category category) {
        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                final ContentValues contentValues = new ContentValues();
                contentValues.put(DbConstants.COLUMN_CATEGORY_NAME, category.getName());
                contentValues.put(DbConstants.COLUMN_CATEGORY_KEY, category.getKey());
                database.update(DbConstants.TABLE_CATEGORY, contentValues, DbConstants.COLUMN_CATEGORY_ID + "=?",
                        new String[]{Integer.toString(category.getId())});
            }
        });
    }

    public void saveNewestFetched(final int channelId, final Date date) {
        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                final ContentValues contentValues = new ContentValues();
                contentValues.put(DbConstants.COLUMN_CHANNEL_NEWEST_FETCHED, Long.toString(date.getTime()));

                database.update(DbConstants.TABLE_CHANNEL, contentValues, DbConstants.COLUMN_CHANNEL_ID + "=?", new String[]{Integer.toString(channelId)});
            }
        });
    }

    public List<RssFeed> findAllWithoutContent() {
        return doWithReadableDatabase(new ActionWithResultWithDatabase() {

            @Override
            public List<RssFeed> doAction(SQLiteDatabase database) {
                String selection = DbConstants.COLUMN_RSS_FEED_CONTENT + " is null AND " + DbConstants.COLUMN_RSS_FEED_CONTENT_ATTEMPTS + " < "
                        + MAX_CONTENT_ATTEMPTS + " AND " + DbConstants.COLUMN_RSS_FEED_URL + " is not null";
                final Cursor cursor = database.query(DbConstants.TABLE_RSS_FEED, null, selection, null, null, null, null, null);
                List<RssFeed> rssList = CursorToListConverterUtil.toRssList(cursor);
                cursor.close();
                return rssList;
            }
        });
    }

    public void editRssFeeds(final List<RssFeed> rssFeeds) {
        doWithWritableDatabase(new ActionWithDatabase() {
            @Override
            public void doAction(SQLiteDatabase database) {
                for (RssFeed rssFeed : rssFeeds) {
                    final ContentValues contentValues = new ContentValues();
                    contentValues.put(DbConstants.COLUMN_RSS_FEED_CONTENT, rssFeed.getContent());
                    contentValues.put(DbConstants.COLUMN_RSS_FEED_CONTENT_ATTEMPTS, rssFeed.getContentAttempts());
                    database.update(DbConstants.TABLE_RSS_FEED, contentValues, DbConstants.COLUMN_RSS_FEED_ID + "=?",
                            new String[]{Integer.toString(rssFeed.getId())});
                }
            }
        });
    }

    public int calculateCountUnreadFeeds() {
        return doWithReadableDatabase(new ActionWithResultWithDatabase() {
            @Override
            public Integer doAction(SQLiteDatabase database) {
                final long l = DatabaseUtils.queryNumEntries(database, DbConstants.TABLE_RSS_FEED,
                        DbConstants.COLUMN_RSS_FEED_READ + "=0");
                return Integer.valueOf((int) l);
            }
        });
    }
}
